using System;
using System.Xml;
using System.IO;
using System.Collections;
using System.Data;
using System.Collections.Generic;
using System.Text;
namespace SSG {
	public class SqlScriptGenerator {
		public static string GenerateSqlInserts(ArrayList aryColumns,DataTable dtTable,string sTargetTableName,string identityColumn) {




			string sSqlInserts=string.Empty;
			StringBuilder sbSqlStatements=new StringBuilder(string.Empty);

			if(identityColumn!=""&&aryColumns.Contains(identityColumn))
			{
				sbSqlStatements.AppendFormat("set identity_insert {0} on",sTargetTableName);
				sbSqlStatements.AppendLine();

			}


			StringBuilder sbColumns=new StringBuilder(string.Empty);
			string[] EscColumns=new string[aryColumns.Count];
			int cnt=0;
			foreach(string col in aryColumns)
			{
				EscColumns[cnt++]=Utils.EscapeKeywords(col);

			}
			sbColumns.Append(string.Join(",",EscColumns));


			// loop thru each record of the datatable
			foreach(DataRow drow in dtTable.Rows)
			{
				// loop thru each column, and include the value if the column is in the array
				StringBuilder sbValues=new StringBuilder(string.Empty);
				foreach(string col in aryColumns)
				{
					if(sbValues.ToString()!=string.Empty)
						sbValues.Append(",");
					// need to do a case to check the column-value types(quote strings(check for dups first), convert bools)
					sbValues.Append(DataRowToSqlString(drow[col]));
				}
				string snewsql=string.Format("insert into {0}({1})",Utils.EscapeKeywords(sTargetTableName),sbColumns.ToString());
				sbSqlStatements.Append(snewsql);
				//if(snewsql.Length>65)
				//{
				//sbSqlStatements.AppendLine();
				//sbSqlStatements.Append('\t');
				//}
				snewsql=string.Format("values({0})",sbValues.ToString());
				sbSqlStatements.Append(snewsql);
				sbSqlStatements.AppendLine();
				//sbSqlStatements.AppendLine();
			}
			sSqlInserts=sbSqlStatements.ToString();
			return sSqlInserts;
		}
		static string DataRowToSqlString(object rowObj) {
			StringBuilder sb=new StringBuilder();
			Type t=rowObj.GetType();
			if(t==typeof(bool))
				sb.Append(Convert.ToBoolean(rowObj)?"1":"0");
			else if(t==typeof(string))
				sb.Append(string.Format("'{0}'",QuoteSQLString(rowObj)));
			else if(t==typeof(DateTime))
				sb.AppendFormat("convert(DateTime,'{0:yyyy-MM-dd HH:mm:ss}',120)",rowObj);
			else if(t==typeof(byte[]))
				sb.Append(string.Format("'{0}'",Convert.ToBase64String((byte[])rowObj)));
			else if(t==typeof(DBNull))
				sb.Append("NULL");
			else
				sb.Append(rowObj);
			return sb.ToString();
		}
		public static string GenerateSqlUpdates(ArrayList aryColumns,ArrayList aryWhereColumns,DataTable dtTable,string sTargetTableName) {
			string sSqlUpdates=string.Empty;
			StringBuilder sbSqlStatements=new StringBuilder();
			StringBuilder sbColumns=new StringBuilder(string.Empty);
			// UPDATE table SET col1 = 3, col2 = 4 WHERE (select cols)
			// loop thru each record of the datatable
			foreach(DataRow drow in dtTable.Rows)
			{
				// VALUES clause:  loop thru each column, and include the value if the column is in the array
				StringBuilder sbValues=new StringBuilder();
				foreach(string col in aryColumns)
				{
					StringBuilder sbNewValue=new StringBuilder(col+"=");
					if(sbValues.ToString()!=string.Empty)
						sbValues.Append(",");
					// need to do a case to check the column-value types(quote strings(check for dups first), convert bools)
					sbNewValue.Append(DataRowToSqlString(drow[col]));
					sbValues.Append(sbNewValue.ToString());
				}
				// WHERE clause:  loop thru each column, and include the value if the column is in the array
				StringBuilder sbWhereValues=new StringBuilder();
				foreach(string col in aryWhereColumns)
				{
					StringBuilder sbNewValue=new StringBuilder(col+"=");
					if(sbWhereValues.ToString()!=string.Empty)
						sbWhereValues.Append(" AND ");
					// need to do a case to check the column-value types(quote strings(check for dups first), convert bools)
					sbNewValue.Append(DataRowToSqlString(drow[col]));
					sbWhereValues.Append(sbNewValue.ToString());
				}
				// UPDATE table SET col1 = 3, col2 = 4 WHERE (select cols)
				// write the line out to the stringbuilder
				string snewsql=string.Format("UPDATE {0} SET {1} WHERE {2};",Utils.EscapeKeywords(sTargetTableName),sbValues.ToString(),sbWhereValues.ToString());
				sbSqlStatements.Append(snewsql);
				//sbSqlStatements.AppendLine();
				sbSqlStatements.AppendLine();
			}
			sSqlUpdates=sbSqlStatements.ToString();
			return sSqlUpdates;
		}
		public static string GenerateSqlDeletes(ArrayList aryColumns,DataTable dtTable,string sTargetTableName) {
			string sSqlDeletes=string.Empty;
			StringBuilder sbSqlStatements=new StringBuilder(string.Empty);
			// loop thru each record of the datatable
			foreach(DataRow drow in dtTable.Rows)
			{
				// loop thru each column, and include the value if the column is in the array
				StringBuilder sbValues=new StringBuilder(string.Empty);
				foreach(string col in aryColumns)
				{
					StringBuilder sbNewValue=new StringBuilder(col+"=");
					if(sbValues.ToString()!=string.Empty)
						sbValues.Append(" AND ");
					// need to do a case to check the column-value types(quote strings(check for dups first), convert bools)
					sbNewValue.Append(DataRowToSqlString(drow[col]));
					sbValues.Append(sbNewValue.ToString());
				}
				string snewsql=string.Format("DELETE {0} WHERE {1};",Utils.EscapeKeywords(sTargetTableName),sbValues.ToString());
				sbSqlStatements.Append(snewsql);
				sbSqlStatements.AppendLine();
			}
			sSqlDeletes=sbSqlStatements.ToString();
			return sSqlDeletes;
		}
		static string QuoteSQLString(object ostr) {
			return ostr.ToString().Trim().Replace("'","''");
		}
	}
}
